﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

namespace Nova_DAL
{
    public class TSysoption
    {
        string strSQL = "";
        DbHelper dbHelper = new DbHelper();
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        DbCommand cmd;
        string strColor = "";

        ///<summary>
        ///获取货位存储类型信息
        ///TSysoption表的存储类型的值是104,这个值是固定值
        ///</summary>
        ///<param name="sQL">SQL</param>
        ///<returns>返回dataset</returnss>
        ///Writer：[王成友]
        ///Create Date： [2010-7-8]
        ///</summary>
        public DataSet DataListTSysoption(int iDisOne)
        {
            try
            {
                strSQL = @"select DisTwo,Valuefloat,Valuechar,Valueint from TSysoption where DisOne=@DisOne ";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@DisOne", DbType.Int32, iDisOne);
                ds = dbHelper.ExecuteDataSet(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return ds;
        }
        /// <summary>
        /// 获取尺寸 表示名称、数值
        /// </summary>
        /// <param name="iDisOne"></param>
        /// <returns></returns>
        public DataSet DataListTSysoptionSize(int iDisOne)
        {
            try
            {
                strSQL = @"select Valuechar,Valueint from TSysoption where DisOne=@DisOne";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@DisOne", DbType.Int32, iDisOne);
                ds = dbHelper.ExecuteDataSet(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return ds;
        }

        /// <summary>
        /// 获取尺寸 表示名称、数值
        /// 通过iDisTwo ！=0 可以排除初始值
        /// </summary>
        /// <param name="iDisOne"></param>
        /// <param name="iDisTwo">0:初始值  1：配置数据项</param>
        /// <returns></returns>
        public DataSet DataListTSysoptionSize(int iDisOne, int iDisTwo)
        {
            try
            {
                strSQL = @"select Valuechar,Valueint from TSysoption where DisOne=@DisOne and DisTwo=@DisTwo ";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@DisOne", DbType.Int32, iDisOne);
                dbHelper.AddInParameter(cmd, "@DisTwo", DbType.Int32, iDisTwo);
                ds = dbHelper.ExecuteDataSet(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return ds;
        }

        /// <summary>
        /// 获取品质状态视图
        /// </summary>
        /// <param name="strLaneID">巷道号</param>
        /// <returns></returns>
        public DataSet TSysoptionList(string strLaneID)
        {
            try
            {
                strSQL = @"exec [Sp_TypeOfStatePosition] @LaneID";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@LaneID", DbType.String, strLaneID);
                ds = dbHelper.ExecuteDataSet(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return ds;
        }

        ///<summary>
        ///获取货位存储类型信息表的状态颜色
        ///TSysoption表的存储类型的值是109,这个值是固定值
        ///</summary>
        ///<param name="sQL">SQL</param>
        ///<returns>返回dataset</returnss>
        ///Writer：[王成友]
        ///Create Date： [2010-7-8]
        ///</summary>
        public string getColor(int iDisOne, int N)
        {
            try
            {
                strSQL = "select Valuefloat from TSysoption where DisOne=@DisOne and Valueint =@Valueint";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@DisOne", DbType.Int32, iDisOne);
                dbHelper.AddInParameter(cmd, "@Valueint", DbType.Int32, N);
                strColor = (string)dbHelper.ExecuteScalar(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());

            }
            return strColor;

        }

        /// <summary>
        /// 获取货位尺寸视图
        /// </summary>
        /// <param name="strProName">存储过程名字</param>
        /// <param name="strLaneID">巷道号</param>
        /// <returns></returns>
        public DataSet TSysoptionList_Size(string strProName, string strLaneID)
        {
            try
            {
                strSQL = @"exec " + strProName + " @LaneID";
                cmd = dbHelper.GetSqlStringCommond(strSQL);
                dbHelper.AddInParameter(cmd, "@LaneID", DbType.String, strLaneID);
                ds = dbHelper.ExecuteDataSet(cmd);
            }
            catch (Exception ex)
            {
                Console.Write(ex.ToString());
            }
            return ds;
        }

        public DataSet SysOptionList_ABCKind(string strLaneID,int subArea)
        {
            strSQL = "select [No_Line] as [【" + strLaneID + "】],";
            if (subArea == 1)
            {
                strSQL += @"[No_Layer] as 巷道,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],
                [26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52],[53],[54],[55],[56],[57],[58],[59],[60]
                FROM (SELECT [No_Line] ,[No_Layer],[No_Col],
                CASE [ABC_Kind] WHEN 0 THEN 'A' WHEN 1 THEN 'B' WHEN 3 THEN 'C' WHEN 4 THEN 'D' ELSE 'D' END ABC_Kind
                ,[No_Lane] FROM [TBase_Position] where No_Lane=@LaneID AND Pos_State <>8) AS T 
                 PIVOT(max([ABC_Kind]) FOR [No_Col] IN ( [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25],
                 [26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52],[53],[54],[55],[56],[57],[58],[59],[60]
                )) AS thePivot ORDER BY [No_Line] ASC ";
            }
            else
            {
                strSQL += @"[No_Layer] as 巷道,[61],[62],[63],[64],[65],[66],[67],[68],[69],[70],[71],[72],[73],[74],[75],[76],[77],[78],[79],[80],[81],[82],[83],[84],[85],
                [86],[87],[88],[89],[90],[91],[92],[93],[94],[95],[96],[97],[98],[99],[100],[101],[102],[103],[104],[105],[106],[107],[108],[109],[110],[111],[112],[113],[114],[115],[116],[117],[118],[119]
                FROM (SELECT [No_Line] ,[No_Layer],[No_Col],
                CASE [ABC_Kind] WHEN 0 THEN 'A' WHEN 1 THEN 'B' WHEN 3 THEN 'C' WHEN 4 THEN 'D' ELSE 'D' END ABC_Kind
                ,[No_Lane] FROM [TBase_Position] where No_Lane=@LaneID AND Pos_State <>8) AS T 
                 PIVOT(max([ABC_Kind]) FOR [No_Col] IN ( [61],[62],[63],[64],[65],[66],[67],[68],[69],[70],[71],[72],[73],[74],[75],[76],[77],[78],[79],[80],[81],[82],[83],[84],[85],
                [86],[87],[88],[89],[90],[91],[92],[93],[94],[95],[96],[97],[98],[99],[100],[101],[102],[103],[104],[105],[106],[107],[108],[109],[110],[111],[112],[113],[114],[115],[116],[117],[118],[119]
                )) AS thePivot ORDER BY [No_Line] ASC ";
            }
            cmd = dbHelper.GetSqlStringCommond(strSQL);
            dbHelper.AddInParameter(cmd, "@LaneID", DbType.String, strLaneID);
            ds = dbHelper.ExecuteDataSet(cmd);
            return ds;
        }
    }
}
